package com.yenlien.traceyun.control.dao;


import com.yenlien.traceyun.control.entity.LoanInfo;
import com.yenlien.traceyun.control.entity.Repayment;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

public interface RepaymentDao extends BaseJpaDao<Repayment> {

    @Query(value = "select id,actual_repayment_time,amount,create_time,loan_period,name,rate,remarks," +
            "repayment_num,repayment_time,reviewer,loan_id," +
            "status,surplus_num from t_repayment where loan_id=?1 order by surplus_num ASC", nativeQuery = true)
    List<Repayment> findAllAndAscNUm(String loanId);

    //待完善
    @Query(value = "select tr.id,tr.actual_repayment_time,tr.amount,tr.create_time,tr.loan_period,tr.name," +
            "tr.rate,tr.remarks,tr.repayment_num,tr.repayment_time,tr.reviewer,tr.status,tr.surplus_num," +
            "tr.loan_id,tr.real_amount,tr.surplus_amount,lf.name as applyName, lf.create_time as applyTime,lf.amount as applyAmount," +
            "(select count(tr.id) from t_repayment tr inner join t_loan_info lf on lf.id=tr.loan_id) as total" +
            " from t_repayment tr inner join t_loan_info lf on lf.id=tr.loan_id limit ?1,?2", nativeQuery = true)
    List<Map<String, Object>> findByAll(int page, int size);


    @Query(value = "select tr.id,tr.actual_repayment_time,tr.amount,tr.create_time,tr.loan_period,tr.name," +
            "tr.rate,tr.remarks,tr.repayment_num,tr.repayment_time,tr.reviewer,tr.status,tr.surplus_num," +
            "tr.loan_id,tr.real_amount,tr.surplus_amount,lf.name as applyName, lf.create_time as applyTime,lf.amount as applyAmount " +
            " from t_repayment tr inner join t_loan_info lf on lf.id=tr.loan_id where tr.id=?1", nativeQuery = true)
    List<Map<String, Object>> findById2(String id);

    @Transactional
    @Modifying
    @Query(value = "update t_repayment set actual_repayment_time=?2,real_amount=?3,status=?4 where id=?1", nativeQuery = true)
    Integer updateRepay(String id, Date time, BigDecimal realAmount, String status);

    @Transactional
    @Modifying
    @Query(value = "update t_repayment set status=?2,actual_repayment_time=?3 where id=?1", nativeQuery = true)
    Integer updateRepay2(String id, String status, Date time);


    @Transactional
    @Modifying
    @Query(value = "update t_repayment set surplus_amount=?2,repaid_amount=?3 where loan_id=?1", nativeQuery = true)
    Integer updateRepay3(String loan_id, BigDecimal surplusAm, BigDecimal repaidAmount);


    @Query(value = "select id,actual_repayment_time,amount,create_time,name,rate,remarks," +
            "repayment_num,repayment_time,reviewer,status,loan_period,surplus_amount," +
            "company_id,loan_id,bank_info_id,repaid_amount,real_amount,surplus_num from t_repayment where loan_id=?1 and status='0'" +
            " order by surplus_num ASC", nativeQuery = true)
    List<Repayment> findByLoanId(String loanId);

    @Query(value = "select sum(real_amount) as realAmount from t_repayment where loan_id=?1", nativeQuery = true)
    Integer findByLoanId2(String loanId);

    @Query(value = "select id,actual_repayment_time,amount,create_time,name,rate,remarks," +
            "repayment_num,repayment_time,reviewer,status,loan_period,surplus_amount," +
            "company_id,loan_id,bank_info_id,real_amount,repaid_amount,surplus_num from t_repayment where loan_id=?1" +
            " order by surplus_num DESC", nativeQuery = true)
    List<Repayment> findByLoanId3(String loanId);

    @Query(value = "select sum(amount) as amount from t_repayment where loan_id=?1", nativeQuery = true)
    Integer findByTotalAmout(String id);

    //统计当前申请订单下有多少还款数
    @Query(value = "select count(id) as totalNum from t_repayment where loan_id=?1 and status='2'", nativeQuery = true)
    Integer findBySumNum(String loanId);

    @Query(value = "select sum(repaid_amount) as totalNum from t_repayment where loan_id=?1 and status='2'", nativeQuery = true)
    Integer findBySumNum2(String loanId);
}
